package entity

import (
	"fmt"
	"strconv"
	"time"

	"gorm.io/gorm"
	"shqsoft.cn/pas3ui/base"
)

//ShIndiDjzbdr 零售定级指标导入
type ShIndiDjzbdr struct {
	ID     int       `gorm:"AUTO_INCREMENT;primary_key;column:ID"`
	DTID   time.Time `gorm:"column:DTID"`
	EMID   int       `gorm:"column:EMID"`
	EMNO   string    `gorm:"column:EMNO"`
	EMNM   string    `gorm:"column:EMNM"`
	GJJZH  float64   `gorm:"column:GJJZH"`
	XSZS   float64   `gorm:"column:XSZS"`
	XYKFQ  float64   `gorm:"column:XYKFQ"`
	XYKSS  float64   `gorm:"column:XYKSS"`
	DFJE   float64   `gorm:"column:DFJE"`
	SJYHXZ float64   `gorm:"column:SJYHXZ"`
}

//TableName
func (ShIndiDjzbdr) TableName() string {
	return "SH_INDI_DJZBDR"
}

// BeforeCreate
func (u *ShIndiDjzbdr) BeforeCreate(tx *gorm.DB) (err error) {
	var emid *int
	tx.Raw(`select "ID" from "T_SysUser" where "SysUserID" = ?`, u.EMNO).Row().Scan(&emid)
	if emid == nil {
		err = fmt.Errorf("员工号%s在系统中不存在！", u.EMNO)
		return
	}
	u.EMID = *emid
	return
}

//ShIndiDjzbdrAfterImport 导入后执行分户处理
func ShIndiDjzbdrAfterImport(tx *gorm.DB, params map[string]string) (err error) {
	dtidStr := params["DTID"]
	n := dtidStr[0:4]
	y := dtidStr[4:6]
	dtid, err := time.Parse("2006-01-02", dtidStr)
	if err != nil {
		return err
	}
	nian, _ := strconv.Atoi(n)
	yue, _ := strconv.Atoi(y)
	//年初、季末
	dnc := strconv.Itoa(nian) + "-01-01"
	var djm, sdate, edate string
	stid, _ := strconv.Atoi(dnc[0:4] + dnc[5:7])
	//去年初、去年末
	qnian := nian - 1
	qnc := strconv.Itoa(qnian) + "-01-01"
	qnm := strconv.Itoa(qnian) + "-12-31"
	qstid, _ := strconv.Atoi(qnc[0:4] + qnc[5:7])
	qetid, _ := strconv.Atoi(qnm[0:4] + qnm[5:7])
	var jidu, qdays, ddays int
	if yue == 1 || yue == 2 || yue == 3 {
		jidu = 1
		djm = strconv.Itoa(nian) + "-03-31"
	} else if yue == 4 || yue == 5 || yue == 6 {
		jidu = 2
		djm = strconv.Itoa(nian) + "-06-30"
	} else if yue == 7 || yue == 8 || yue == 9 {
		jidu = 3
		djm = strconv.Itoa(nian) + "-09-30"
	} else if yue == 10 || yue == 11 || yue == 12 {
		jidu = 4
		djm = strconv.Itoa(nian) + "-12-31"
	}
	if jidu == 1 || jidu == 2 {
		sdate = strconv.Itoa(nian) + "-01-01"
		edate = strconv.Itoa(nian) + "-06-30"
	} else if jidu == 3 || jidu == 4 {
		sdate = strconv.Itoa(nian) + "-07-01"
		edate = strconv.Itoa(nian) + "-12-31"
	}
	etid, _ := strconv.Atoi(djm[0:4] + djm[5:7])
	sqlStr := `SELECT dateDiff('day',toDate(?) , toDate(?))+1`
	base.CHDB.Raw(sqlStr, dnc, djm).Row().Scan(&ddays)
	base.CHDB.Raw(sqlStr, qnc, qnm).Row().Scan(&qdays)
	sqlStr = `ALTER TABLE SH_KHJL_DJ_BB DELETE WHERE NIAN=? AND JIDU=?`
	err = base.CHDB.Exec(sqlStr, nian, jidu).Error
	if err != nil {
		return
	}
	sqlStr = `INSERT INTO SH_KHJL_DJ_BB (EMID,NIAN,JIDU,ZBID,WCZ,DF)
				SELECT EMID,?,?,31,IF(SUM(A.DCXRJ-B.QCXRJ)>0,SUM(B.QCXRJ),SUM(A.DCXRJ)) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(31))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(31))) AS DF
				FROM
				(SELECT EMID,sumMerge(ACBL_CX)/? DCXRJ
				FROM MFKB_EMPL_INDEX WHERE MONT>=? AND MONT<=?
				GROUP BY EMID) A FULL JOIN
				(SELECT EMID,sumMerge(ACBL_CX)/? QCXRJ
				FROM MFKB_EMPL_INDEX WHERE MONT>=? AND MONT<=?
				GROUP BY EMID) B ON A.EMID=B.EMID
				GROUP BY EMID
				UNION ALL
				SELECT EMID,?,?,32,IF(SUM(A.DCXRJ-B.QCXRJ)>0,SUM(A.DCXRJ-B.QCXRJ),0) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(32))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(32))) AS DF
				FROM
				(SELECT EMID,sumMerge(ACBL_CX)/? DCXRJ
				FROM MFKB_EMPL_INDEX WHERE MONT>=? AND MONT<=?
				GROUP BY EMID) A FULL JOIN
				(SELECT EMID,sumMerge(ACBL_CX)/? QCXRJ
				FROM MFKB_EMPL_INDEX WHERE MONT>=? AND MONT<=?
				GROUP BY EMID) B ON A.EMID=B.EMID
				GROUP BY EMID
				UNION ALL
				SELECT EMID,?,?,34,sumMerge(SALE_LS_FBBLC) WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(34))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(34))) AS DF
				FROM MFKB_EMPL_INDEX WHERE MONT>=? AND MONT<=?
				GROUP BY EMID
				UNION ALL
				SELECT EMID,?,?,35,toFloat64(IF(SUM(DBXKH)>0,SUM(DBXKH),0)) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(35))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(35))) AS DF
				FROM (SELECT EMID,SUM(BXKH) AS DBXKH
				FROM T_INDI_CLIE_BXKH A INNER JOIN (SELECT CUID,DIVI.EMID EMID,DIVI.DSVL DSVL FROM T_INDI_CLIE_ACBL ARRAY JOIN DIVI WHERE DTID = ?) B ON A.CUID = B.CUID 
				WHERE A.DTID = ?
				GROUP BY EMID) A FULL JOIN
				(SELECT EMID,SUM(BXKH) AS QBXKH
				FROM T_INDI_CLIE_BXKH A INNER JOIN (SELECT CUID,DIVI.EMID EMID,DIVI.DSVL DSVL FROM T_INDI_CLIE_ACBL ARRAY JOIN DIVI WHERE DTID = ?) B ON A.CUID = B.CUID 
				WHERE A.DTID = ?
				GROUP BY EMID) B ON A.EMID=B.EMID
				GROUP BY EMID
				UNION ALL
				SELECT EMID,?,?,23,WCZ,DF
				FROM (SELECT EMID,KDID,
				SUM(ACBL1) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(23))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(23))) AS DF,
				dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
				FROM pas_olap_nj.DF_CM_RETAIL_LOAN_DUBIL
				WHERE DTID = ? AND KDNO IN(1,4,5) AND PERD IN(24,36,48,60,72,84,96,120)
				GROUP BY EMID,KDID) T
				UNION ALL
				SELECT EMID,?,?,24,WCZ,DF
				FROM (SELECT EMID,KDID,
				SUM(ACBL1) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(24))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(24))) AS DF,
				dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
				FROM pas_olap_nj.DF_CM_RETAIL_LOAN_DUBIL
				WHERE DTID = ? AND KDNO=2 AND PERD IN(24,36,48,60,72,84,96,120)
				GROUP BY EMID,KDID) T
				UNION ALL
				SELECT EMID,?,?,25,WCZ,DF
				FROM (SELECT EMID,KDID,
				SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(25))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(25))) AS DF,
				dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
				FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
				(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
				WHERE SDATE<=? AND EDATE>=?) B ON A.ACID = B.ACID
				WHERE DISTR_DT>=? AND DISTR_DT<=? AND KDNO IN(1,5) AND toFloat64(FLOAT_SPREAD_RATE)*1.2<60
				GROUP BY EMID,KDID) T
				UNION ALL
				SELECT EMID,?,?,26,WCZ,DF
				FROM (SELECT EMID,KDID,
				SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(26))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(26))) AS DF,
				dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
				FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
				(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
				WHERE SDATE<=? AND EDATE>=?) B ON A.ACID = B.ACID
				WHERE DISTR_DT>=? AND DISTR_DT<=? AND KDNO IN(1,5) AND toFloat64(FLOAT_SPREAD_RATE)*1.2>=60
				GROUP BY EMID,KDID) T
				UNION ALL
				SELECT EMID,?,?,28,
				SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(28))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(28))) AS DF
				FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
				(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
				WHERE SDATE<=? AND EDATE>=?) B ON A.ACID = B.ACID
				WHERE DISTR_DT>=? AND DISTR_DT<=? AND toFloat64(FLOAT_SPREAD_RATE)*1.2<80
				AND A.ACID IN (SELECT toUInt64(ACID) FROM mysql.SH_INDI_PHJR WHERE DRRQ=(SELECT MAX(DRRQ) FROM mysql.SH_INDI_PHJR WHERE DRRQ<=?))
				GROUP BY EMID
				UNION ALL
				SELECT EMID,?,?,29,
				SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(29))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(29))) AS DF
				FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
				(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
				WHERE SDATE<=? AND EDATE>=?) B ON A.ACID = B.ACID
				WHERE DISTR_DT>=? AND DISTR_DT<=? AND toFloat64(FLOAT_SPREAD_RATE)*1.2>=80
				AND A.ACID IN (SELECT toUInt64(ACID) FROM mysql.SH_INDI_PHJR WHERE DRRQ=(SELECT MAX(DRRQ) FROM mysql.SH_INDI_PHJR WHERE DRRQ<=?))
				GROUP BY EMID
				UNION ALL
				SELECT EMID,?,?,30,WCZ,DF
				FROM (SELECT EMID,KDID,
				SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
				WCZ/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(30))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(30))) AS DF,
				dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
				FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
				(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
				WHERE SDATE<=? AND EDATE>=?) B ON A.ACID = B.ACID
				WHERE DISTR_DT>=? AND DISTR_DT<=? AND KDNO NOT IN(1,5)
				AND A.ACID NOT IN (SELECT toUInt64(ACID) FROM mysql.SH_INDI_PHJR WHERE DRRQ=(SELECT MAX(DRRQ) FROM mysql.SH_INDI_PHJR WHERE DRRQ<=?))
				GROUP BY EMID,KDID) T`
	err = base.CHDB.Exec(sqlStr, nian, jidu, ddays, stid, etid, qdays, qstid, qetid, nian, jidu, ddays, stid, etid, qdays, qstid, qetid,
		nian, jidu, stid, etid, nian, jidu, djm, djm, qnm, qnm, nian, jidu, dnc, nian, jidu, dnc, nian, jidu, djm, djm, sdate, edate,
		nian, jidu, djm, djm, sdate, edate, nian, jidu, djm, djm, sdate, edate, djm, nian, jidu, djm, djm, sdate, edate, djm,
		nian, jidu, djm, djm, sdate, edate, djm).Error
	if err != nil {
		return
	}
	sqlStr = `INSERT INTO SH_KHJL_DJ_BB (EMID,NIAN,JIDU,ZBID,WCZ,DF)
			  SELECT EMID,?,?,27,toFloat64(GJJZH) AS WCZ,toFloat64(GJJZH)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(27))*
			  (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(27))) AS DF FROM mysql.SH_INDI_DJZBDR WHERE DTID=?
			  UNION ALL
			  SELECT EMID,?,?,33,toFloat64(XSZS) AS WCZ,toFloat64(XSZS)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(33))*
			  (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(33))) AS DF FROM mysql.SH_INDI_DJZBDR WHERE DTID=?
			  UNION ALL
			  SELECT EMID,?,?,36,toFloat64(XYKFQ) AS WCZ,toFloat64(XYKFQ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(36))*
			  (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(36))) AS DF FROM mysql.SH_INDI_DJZBDR WHERE DTID=?
			  UNION ALL
			  SELECT EMID,?,?,37,toFloat64(XYKSS) AS WCZ,toFloat64(XYKSS)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(37))*
			  (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(37))) AS DF FROM mysql.SH_INDI_DJZBDR WHERE DTID=?
			  UNION ALL
			  SELECT EMID,?,?,38,toFloat64(DFJE) AS WCZ,toFloat64(DFJE)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(38))*
			  (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(38))) AS DF FROM mysql.SH_INDI_DJZBDR WHERE DTID=?
			  UNION ALL
			  SELECT EMID,?,?,39,toFloat64(SJYHXZ) AS WCZ,toFloat64(SJYHXZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(39))*
			  (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(39))) AS DF FROM mysql.SH_INDI_DJZBDR WHERE DTID=?`
	err = base.CHDB.Exec(sqlStr, nian, jidu, dtid, nian, jidu, dtid, nian, jidu, dtid, nian, jidu, dtid, nian, jidu, dtid, nian, jidu, dtid).Error
	if err != nil {
		return
	}
	return
}

//ShIndiDjzbdrEF 返回结构体
func ShIndiDjzbdrEF() base.EntityFunc {
	return func() interface{} {
		entity := ShIndiDjzbdr{}
		return &entity
	}
}

//ShIndiDjzbdrESF 返回结构体数组
func ShIndiDjzbdrESF() base.EntitiesFunc {
	return func() interface{} {
		var entities []ShIndiDjzbdr
		return &entities
	}
}

func init() {
	base.DaoPoolObj.Put("ShIndiDjzbdr", base.NewDefaultDao(ShIndiDjzbdrEF(), ShIndiDjzbdrESF(), nil, "my"))
	base.SetAfterCallBack("ShIndiDjzbdr", ShIndiDjzbdrAfterImport)
}
